use sqlite_wasm_rs::sqlite3_stmt;
use crate::db::dao::base_dao::Adder;
use crate::db::db::{Db, DbParamType};
use crate::db::pojo::pojo::{ImGroup, ImGroupUser};
use crate::db::util::{db_get_bool, db_get_int, db_get_int64, db_get_string};
use crate::util::time::parse_to_time;


const SELECT_COLUMNS: &str = "id, currentUserId, code, serverId, name, ownerId, groupType, status, firstLetter, description,
        version, ext1, ext2, createTime, updateTime";


pub fn group_insert(db: Db, group: &mut ImGroup) {
    let sql = "insert into im_group(serverId, code, currentUserId, name, ownerId, groupType, status, firstLetter, description, createTime, updateTime)
       values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    let mut params = vec![];
    params.push(DbParamType::OptionStr(group.server_id.as_ref()));
    params.push(DbParamType::Str(&*group.code));
    params.push(DbParamType::Str(&*group.current_user_id));
    params.push(DbParamType::OptionStr(group.name.as_ref()));
    params.push(DbParamType::Str(&*group.owner_id));
    params.push(DbParamType::Byte(group.group_type.clone() as i8));
    params.push(DbParamType::Byte(group.status.clone() as i8));
    params.push(DbParamType::OptionStr(group.first_letter.as_ref()));
    params.push(DbParamType::OptionStr(group.description.as_ref()));
    params.push(DbParamType::DateTime2(&group.create_time));
    params.push(DbParamType::DateTime2(&group.update_time));

    let id = db.insert("group insert", sql, params);
    group.id = Some(id);
}

pub fn group_inserts(db: Db, groups: &mut Vec<&mut ImGroup>) {
    let sql = "insert into im_group(serverId, code, currentUserId, name, ownerId, groupType, status, firstLetter, description, createTime, updateTime)
       values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    let mut params_list = vec![];
    for group in groups.iter() {
        let mut params = vec![];
        params.push(DbParamType::OptionString(group.server_id.clone()));
        params.push(DbParamType::String(group.code.clone()));
        params.push(DbParamType::Str(&*group.current_user_id));
        params.push(DbParamType::OptionStr(group.name.as_ref()));
        params.push(DbParamType::Str(&*group.owner_id));
        params.push(DbParamType::Byte(group.group_type.clone() as i8));
        params.push(DbParamType::Byte(group.status.clone() as i8));
        params.push(DbParamType::OptionStr(group.first_letter.as_ref()));
        params.push(DbParamType::OptionStr(group.description.as_ref()));
        params.push(DbParamType::DateTime2(&group.create_time));
        params.push(DbParamType::DateTime2(&group.update_time));

        params_list.push(params);
    }


    let ids = db.inserts2("group inserts", sql, params_list);
    assert_eq!(ids.len(), groups.len(), "批量插入数据没有都生成主键(group)");
    for (idx, value) in ids.iter().enumerate() {
        let group = groups.get_mut(idx).unwrap();
        group.id = Some(value.clone());
    }
}

pub fn group_update(db: Db, group: &mut ImGroup) {
    let sql = "update im_group set serverId = ?, code = ?, name = ?, description = ?, ownerId = ?, status = ?, firstLetter = ?,
       version = version + 1, updateTime = ? where id = ?";

    let mut params = vec![];
    params.push(DbParamType::OptionStr(group.server_id.as_ref()));
    params.push(DbParamType::Str(&*group.code));
    params.push(DbParamType::OptionStr(group.name.as_ref()));
    params.push(DbParamType::OptionStr(group.description.as_ref()));
    params.push(DbParamType::Str(&*group.owner_id));
    params.push(DbParamType::Byte(group.status.clone() as i8));
    params.push(DbParamType::OptionStr(group.first_letter.as_ref()));
    params.push(DbParamType::DateTime2(&group.update_time));
    params.push(DbParamType::Uint(group.id.clone().unwrap()));

    let id = db.insert("group update", sql, params);
    group.id = Some(id);
}

pub fn group_updates(db: Db, group: &mut Vec<&mut ImGroup>) {
    let sql = "update im_group set serverId = ?, code = ?, name = ?, description = ?, ownerId = ?, status = ?, firstLetter = ?,
       version = version + 1, updateTime = ? where id = ?";

    let mut params_list = vec![];
    for group in group.iter() {
        let mut params = vec![];

        params.push(DbParamType::OptionString(group.server_id.clone()));
        params.push(DbParamType::Str(&*group.code));
        params.push(DbParamType::OptionStr(group.name.as_ref()));
        params.push(DbParamType::OptionStr(group.description.as_ref()));
        params.push(DbParamType::Str(&*group.owner_id));
        params.push(DbParamType::Byte(group.status.clone() as i8));
        params.push(DbParamType::OptionStr(group.first_letter.as_ref()));
        params.push(DbParamType::DateTime2(&group.update_time));
        params.push(DbParamType::Uint(group.id.clone().unwrap()));
        
        params_list.push(params);
    }

    db.batch_exec("group updates", sql.to_string(), params_list);
}

pub fn get_need_send_groups_by_user_id(db: Db, user_id: String, size: usize)->Vec<ImGroup> {
    let sql = format!("select {} FROM im_group where currentUserId = ? limit 0, {}", SELECT_COLUMNS, size);
    let mut params = vec![];
    params.push(DbParamType::String(user_id));
    let mut groups = db.query2("group query", sql.as_str(), params, Box::new(group_wrap));
    for group in groups.iter_mut() {
        let group_users = group_user_query_by_group_id(db.clone(), group.id.unwrap());
        group.group_users = Some(group_users);
    }
    groups
}

pub fn query_groups_by_group_codes(db: Db, group_codes: Vec<String>) -> Vec<ImGroup> {
    if group_codes.is_empty() { return Vec::new(); }
    let sql_where = group_codes.iter().map(|_code| "?").collect::<Vec<&str>>().join(",");
    let sql = format!("select id, currentUserId, code, serverId, name, ownerId, groupType, status, firstLetter, description,
        version, ext1, ext2, createTime, updateTime FROM im_group where code in ({})", sql_where);
    let mut params = vec![];
    group_codes.into_iter().for_each(|code| {
        params.push(DbParamType::String(code));
    });
    db.query2("group query", sql.as_str(), params, Box::new(group_wrap))
}

pub fn query_group_by_group_code(db: Db, group_code: &str) -> Option<ImGroup> {
    let sql = "select id, currentUserId, code, serverId, name, ownerId, groupType, status, firstLetter, description,
        version, ext1, ext2, createTime, updateTime FROM im_group where code = ?";
    let mut params = vec![];
    params.push(DbParamType::Str(group_code));
    db.query_one("group query", sql.to_string(), params, Box::new(group_wrap))
}

pub fn group_total(db: Db)-> usize {
    let size = db.count("group total", c"select count(*) from im_group", Vec::new());
    log::info!("db group count: {}", size);
    size
}



fn group_user_insert(db: Db, group_user: &mut ImGroupUser) {
    let sql = "insert into im_group_user(userId, userName, groupId, remark, isShowName, isWaking,
        isTop, isDisturb, status, ext1, ext2, createTime, updateTime) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    let mut params = vec![];
    params.push(DbParamType::Str(&*group_user.user_id));
    params.push(DbParamType::OptionStr(group_user.user_name.as_ref()));
    params.push(DbParamType::Uint(group_user.group_id.clone()));
    params.push(DbParamType::OptionStr(group_user.remark.as_ref()));
    params.push(DbParamType::Bool(group_user.is_show_name.clone()));
    params.push(DbParamType::Bool(group_user.is_waking.clone()));
    params.push(DbParamType::Bool(group_user.is_top.clone()));
    params.push(DbParamType::Bool(group_user.is_disturb.clone()));
    params.push(DbParamType::Byte(group_user.status.clone() as i8));
    params.push(DbParamType::OptionStr(group_user.ext1.as_ref()));
    params.push(DbParamType::OptionStr(group_user.ext2.as_ref()));
    params.push(DbParamType::DateTime2(&group_user.create_time));
    params.push(DbParamType::DateTime2(&group_user.update_time));

    let id = db.insert("group user insert", sql, params);
    group_user.id = Some(id);
}

pub fn group_user_inserts(db: Db, group_users: &mut Vec<&mut ImGroupUser>) {
    if group_users.is_empty() { return; }

    let sql = "insert into im_group_user(userId, userName, groupId, remark, isShowName, isWaking,
        isTop, isDisturb, status, ext1, ext2, createTime, updateTime) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    let mut params_list = vec![];
    for group_user in group_users.iter() {
        let mut params = vec![];

        params.push(DbParamType::Str(&*group_user.user_id));
        params.push(DbParamType::OptionStr(group_user.user_name.as_ref()));
        params.push(DbParamType::Uint(group_user.group_id.clone()));
        params.push(DbParamType::OptionStr(group_user.remark.as_ref()));
        params.push(DbParamType::Bool(group_user.is_show_name.clone()));
        params.push(DbParamType::Bool(group_user.is_waking.clone()));
        params.push(DbParamType::Bool(group_user.is_top.clone()));
        params.push(DbParamType::Bool(group_user.is_disturb.clone()));
        params.push(DbParamType::Byte(group_user.status.clone() as i8));
        params.push(DbParamType::OptionStr(group_user.ext1.as_ref()));
        params.push(DbParamType::OptionStr(group_user.ext2.as_ref()));
        params.push(DbParamType::DateTime2(&group_user.create_time));
        params.push(DbParamType::DateTime2(&group_user.update_time));

        params_list.push(params);
    }

    let ids = db.inserts2("group user inserts", sql, params_list);
    assert_eq!(ids.len(), group_users.len(), "批量插入数据没有都生成主键(group_user)");
    for (idx, value) in ids.iter().enumerate() {
        let group_user =  group_users.get_mut(idx).unwrap();
        group_user.id = Some(value.clone());
    }
}

pub fn group_user_update(db: Db, group_user: &mut ImGroupUser) {
    let sql = "update im_group_user set userName = ?, remark = ?, isShowName = ?, isWaking = ?, isTop = ?, isDisturb = ?,
        ext1 = ?, ext2 = ?, updateTime = ? where groupId = ? and userId = ?";

    let mut params = vec![];
    params.push(DbParamType::OptionStr(group_user.user_name.as_ref()));
    params.push(DbParamType::OptionStr(group_user.remark.as_ref()));
    params.push(DbParamType::Bool(group_user.is_show_name.clone()));
    params.push(DbParamType::Bool(group_user.is_waking.clone()));
    params.push(DbParamType::Bool(group_user.is_top.clone()));
    params.push(DbParamType::Bool(group_user.is_disturb.clone()));
    params.push(DbParamType::OptionStr(group_user.ext1.as_ref()));
    params.push(DbParamType::OptionStr(group_user.ext2.as_ref()));
    params.push(DbParamType::DateTime2(&group_user.update_time));

    params.push(DbParamType::Uint(group_user.group_id.clone()));
    params.push(DbParamType::Str(&*group_user.user_id));

    db.exec2("group user update", sql.to_string(), params);
}

pub fn group_user_updates(db: Db, group_users: &mut Vec<&mut ImGroupUser>) {
    if group_users.is_empty() { return; }

    let sql = "update im_group_user set userName = ?, remark = ?, isShowName = ?, isWaking = ?, isTop = ?, isDisturb = ?,
        ext1 = ?, ext2 = ?, updateTime = ? where groupId = ? and userId = ?";

    let mut params_list = vec![];
    for group_user in group_users.iter() {
        let mut params = vec![];

        params.push(DbParamType::OptionStr(group_user.user_name.as_ref()));
        params.push(DbParamType::OptionStr(group_user.remark.as_ref()));
        params.push(DbParamType::Bool(group_user.is_show_name.clone()));
        params.push(DbParamType::Bool(group_user.is_waking.clone()));
        params.push(DbParamType::Bool(group_user.is_top.clone()));
        params.push(DbParamType::Bool(group_user.is_disturb.clone()));
        params.push(DbParamType::OptionStr(group_user.ext1.as_ref()));
        params.push(DbParamType::OptionStr(group_user.ext2.as_ref()));
        params.push(DbParamType::DateTime2(&group_user.update_time));

        params.push(DbParamType::Uint(group_user.group_id.clone()));
        params.push(DbParamType::Str(&*group_user.user_id));

        params_list.push(params);
    }

    db.batch_exec("group user updates", sql.to_string(), params_list);
}

pub fn group_user_insert_or_updates(db: Db, group_users: &mut Vec<ImGroupUser>) {
    if group_users.is_empty() { return }
    let group_id = group_users[0].group_id.clone();
    let user_ids = group_users.iter().map(|group_user| &group_user.user_id).collect::<Vec<&String>>();

    let db_group_users = group_user_query_by_group_id_and_user_ids(db.clone(), group_id, user_ids);
    let db_group_user_ids = db_group_users.iter().map(|group_user| &group_user.user_id).collect::<Vec<&String>>();

    let mut group_user_insert_list = Vec::new();
    let mut group_user_update_list = Vec::new();
    for group_user in group_users.iter_mut() {
        let user_id = &group_user.user_id;
        if db_group_user_ids.contains(&user_id) {
            group_user_update_list.push(&mut *group_user);
        } else {
            group_user_insert_list.push(&mut *group_user);
        }
    }

    group_user_inserts(db.clone(), &mut group_user_insert_list);
    group_user_updates(db.clone(), &mut group_user_update_list);
}

pub fn group_user_query_by_group_id(db: Db, group_id: u64)->Vec<ImGroupUser> {
    let sql = "select id, groupId, userId, userName, remark, isWaking, isShowName,
                          isTop, isDisturb, ext1, ext2, status, createTime, updateTime from im_group_user where groupId = ?";

    let mut params = vec![];
    params.push(DbParamType::Uint(group_id));
    db.query2("group user query", sql, params, Box::new(group_user_wrap))
}

pub fn group_user_query_by_group_id_and_user_ids(db: Db, group_id: u64,  user_ids: Vec<&String>) -> Vec<ImGroupUser> {
    if user_ids.is_empty() { return Vec::new(); }
    let sql_where = user_ids.iter().map(|_code| "?").collect::<Vec<&str>>().join(",");
    let sql = format!("select id, groupId, userId, userName, remark, isWaking, isShowName,
                          isTop, isDisturb, ext1, ext2, status, createTime, updateTime from im_group_user where groupId = ? and userId in ({})", sql_where);

    let mut params = vec![];
    params.push(DbParamType::Uint(group_id));
    user_ids.into_iter().for_each(|user_id| {
        params.push(DbParamType::Str(user_id));
    });
    db.query2("group user query", sql.as_str(), params, Box::new(group_user_wrap))
}




fn group_wrap(stmt: *mut sqlite3_stmt) -> ImGroup {
    unsafe {
        let mut adder = Adder::new();

        let id = db_get_int64(stmt, adder.add());
        let current_user_id = db_get_string(stmt, adder.add()).unwrap();
        let code = db_get_string(stmt, adder.add()).unwrap();
        let server_id = db_get_string(stmt, adder.add());
        let name = db_get_string(stmt, adder.add());
        let owner_id = db_get_string(stmt, adder.add()).unwrap();
        let group_type = db_get_int(stmt, adder.add());
        let status = db_get_int(stmt, adder.add());
        let first_letter = db_get_string(stmt, adder.add());
        let description = db_get_string(stmt, adder.add());
        let version = db_get_int64(stmt, adder.add());
        let ext1 = db_get_string(stmt, adder.add());
        let ext2 = db_get_string(stmt, adder.add());
        let create_time_str = db_get_string(stmt, adder.add()).unwrap();
        let update_time_str = db_get_string(stmt, adder.add()).unwrap();
        let group_type2 = serde_json::from_str(group_type.to_string().as_str()).unwrap();
        let status2 = serde_json::from_str(status.to_string().as_str()).unwrap();

        ImGroup {
            id: Some(id),
            current_user_id,
            code,
            server_id,
            name,
            owner_id,
            description,
            version,
            group_type: group_type2,
            status: status2,
            ext1,
            ext2,
            first_letter,
            create_time: parse_to_time(create_time_str),
            update_time: parse_to_time(update_time_str),

            group_users: None,
        }
    }
}


fn group_user_wrap(stmt: *mut sqlite3_stmt) -> ImGroupUser {
    unsafe {
        let mut adder = Adder::new();
        let id = db_get_int64(stmt, adder.add());
        let group_id = db_get_int64(stmt, adder.add());
        let user_id = db_get_string(stmt, adder.add()).unwrap();
        let user_name = db_get_string(stmt, adder.add());
        let remark = db_get_string(stmt, adder.add());
        let is_waking = db_get_bool(stmt, adder.add());
        let is_show_name = db_get_bool(stmt, adder.add());
        let is_top = db_get_bool(stmt, adder.add());
        let is_disturb = db_get_bool(stmt, adder.add());
        let ext1 = db_get_string(stmt, adder.add());
        let ext2 = db_get_string(stmt, adder.add());
        let status = db_get_int(stmt, adder.add());
        let create_time_str = db_get_string(stmt, adder.add()).unwrap();
        let update_time_str = db_get_string(stmt, adder.add()).unwrap();

        let status2 = serde_json::from_str(status.to_string().as_str()).unwrap();

        ImGroupUser {
            id: Some(id),
            group_id,
            user_id,
            user_name,
            remark,
            is_waking,
            is_show_name,
            is_top,
            is_disturb,
            ext1,
            ext2,
            status: status2,
            create_time: parse_to_time(create_time_str),
            update_time: parse_to_time(update_time_str),
        }
    }
}
